package dao.oracle;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import transferobject.User;
import dao.UserDao;

/**
 * User DAO for Oracle database
 * @author Ilya_Poliakov
 */
public class OracleUserDao implements UserDao {

	private static final String ADD_USER_QUERY = "INSERT INTO users VALUES (?, ?, ?, ?, ?)";
	private static final String BILLING_ADDRESS_FIELD_NAME = "billing_address";
	private static final String DELETE_USER_QUERY = "DELETE FROM users WHERE user_id = ?";
	private static final String FIND_USER_QUERY = "SELECT * FROM users WHERE user_id = ?";
	private static final String FULL_NAME_FIELD_NAME = "full_name";
	private static final String LOGIN_FIELD_NAME = "login";
	private static final String PASSWORD_FIELD_NAME = "password";
	private static final String UPDATE_USER_QUERY = "UPDATE users SET full_name = ?, "
			+ "billing_address = ?, login = ?, password = ? WHERE user_id = ?";
	private final OracleConnection connection;

	/**
	 * create new OracleBidDao
	 * @param connection OracleConnection object
	 */
	public OracleUserDao(OracleConnection connection) {
		super();
		this.connection = connection;
	}

	/**
	 * {@inheritDoc}
	 */
	public boolean deleteUser(int userId) {
		boolean result = false;
		try {
			PreparedStatement ps = connection.getConnection().prepareStatement(
					DELETE_USER_QUERY);
			ps.setInt(1, userId);
			result = ps.executeUpdate() > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * {@inheritDoc}
	 */
	public User findUser(int userId) {
		User user = null;
		try {
			PreparedStatement ps = connection.getConnection().prepareStatement(
					FIND_USER_QUERY);
			ps.setInt(1, userId);
			ResultSet rs = ps.executeQuery();
			rs.next();

			String fullName = rs.getString(FULL_NAME_FIELD_NAME);
			String billingAddress = rs.getString(BILLING_ADDRESS_FIELD_NAME);
			String login = rs.getString(LOGIN_FIELD_NAME);
			String password = rs.getString(PASSWORD_FIELD_NAME);
			user = new User(userId, fullName, billingAddress, login, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}

	/**
	 * {@inheritDoc}
	 */
	public int insertUser(User user) {
		int result = 0;
		try {
			PreparedStatement ps = connection.getConnection().prepareStatement(
					ADD_USER_QUERY);
			ps.setInt(1, user.getUserId());
			ps.setString(2, user.getFullName());
			ps.setString(3, user.getBillingAddress());
			ps.setString(4, user.getLogin());
			ps.setString(5, user.getPassword());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * {@inheritDoc}
	 */
	public boolean updateUser(User user) {
		boolean result = false;
		try {
			PreparedStatement ps = connection.getConnection().prepareStatement(
					UPDATE_USER_QUERY);
			ps.setString(1, user.getFullName());
			ps.setString(2, user.getBillingAddress());
			ps.setString(3, user.getLogin());
			ps.setString(4, user.getPassword());
			ps.setInt(5, user.getUserId());
			result = ps.executeUpdate() > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

}
